//
//  SQLHelper.swift
//  eBooks
//
//  Created by JunMing on 2019/11/6.
//  Copyright © 2019 赵俊明. All rights reserved.
//

import FMDB
enum QueryType {
    case deleteFalse
    case deleteTure
    case totalTime
    case saveTure
    case queryAll
}

enum UpdateType {
    case download
    case isDel
    case totalTime
    case chapter
    case issave
}

enum FileStatus {
    // 1、从未下载过
    case never
    // 2、下载过，但是已删除
    case delete
    // 3、已存在
    case exist
}

class SQLHelper: NSObject {
    
    static let share = SQLHelper()
    let fmdb:FMDatabase!
    let lock = NSLock()
    
    //1.重写父类的构造方法
    override init() {
        if !EBookDefault.readBoolByKey("moveSqlite") {
            try! FileHelper.moveDatabase()
            EBookDefault.setBool(true, "moveSqlite")
        }
        
        fmdb = FMDatabase(path: Tools.epubInfoSqlite()!)
        if !fmdb.open() {
            print("数据库打开失败")
            return
        }
        
        // 本地存储
        // (name,author,desc,cover,booktype,date,size,totaltime,isdel,issave,readRate)
        let createSql = "create table if not exists localEpubInfo(name varchar(100),author varchar(200),desc text,cover varchar(300),booktype varchar(10), date varchar(20),size varchar(10),totaltime integer,isdel integer,issave integer,readRate varchar(5))"
        do {
            try fmdb.executeUpdate(createSql, values: nil)
        }catch {
            print(fmdb.lastErrorMessage())
        }
        
        // 我的权益
        // showLastTime:上次最后展示的时间
        let right = "create table if not exists myRight(adType varchar(20),showCount integer, showLastTime varchar(20), totalCount integer)"
        do {
            try fmdb.executeUpdate(right, values: nil)
        }catch {
            print(fmdb.lastErrorMessage())
        }
    }
    
    // MARK: - ********* 搜索数据使用的方法 *********
    /// 这个方法用在本地倒入epub书的时候查询本地是否存，存在的话更新model
    func fetchSigleModel( _ name:String, _ urlname:Bool = false) -> Model? {
        var tempModel:Model?
        do {
            var sqlStr = "SELECT * FROM epubInfo where name = ?"
            if urlname { sqlStr = "SELECT * FROM epubInfo where urlname = ?" }
            let set = try fmdb.executeQuery(sqlStr, values: [name])
            //循环遍历结果
            while set.next() {
                //给字段赋值
                let size = set.string(forColumn: "size")
                let urlName = set.string(forColumn: "urlname")!
                tempModel = Model(urlName,size)
                tempModel!.author = set.string(forColumn: "author")
                tempModel!.cover = set.string(forColumn: "cover")
                tempModel!.desc = set.string(forColumn: "desc")
                tempModel!.booktype = set.string(forColumn: "type")
                tempModel!.date = String(format: "%f", arguments: [Date.currentTime])
            }
        }catch {
            print(fmdb.lastErrorMessage())
        }
        return tempModel
    }
    
    /// 用于搜索🔍mainView，顶部随机推荐数据
    func fetchNamesData(_ count:Int = 10) ->[String] {
        let fetchSql = "SELECT * FROM epubInfo ORDER BY RANDOM() LIMIT \(count)"
        //用于承接所有数据的临时数组
        var tempArray = [String]()
        do {
            let set = try fmdb.executeQuery(fetchSql, values: nil)
            //循环遍历结果
            while set.next() {
                tempArray.append(set.string(forColumn: "name")!)
            }
        }catch let err {
            print(err.localizedDescription)
        }
        
        return tempArray
    }
    
    /// 用于搜索🔍mainList，查询结果
    func fetchSearchResultData(_ bookName:String) ->[JMSearchModel] {
        let fetchSql = "select * from epubInfo where name like ?"
        //用于承接所有数据的临时数组
        var tempArray = [JMSearchModel]()
        do {
            let set = try fmdb.executeQuery(fetchSql, values: ["%%\(bookName)%%"])
            //循环遍历结果
            while set.next() {
                if let name = set.string(forColumn: "name") {
                    let searchModel = JMSearchModel(title: name)
                    searchModel.rightIcon = "jiantou"
                    tempArray.append(searchModel)
                }
            }
        }catch {
            print(fmdb.lastErrorMessage())
        }
        
        return tempArray
    }
    
    // MARK: - ********* 本地数据操作 *********
    func insertLocalDB(_ model:Model) {
        let fileStatus = isHasDataInLocalDB(model: model)
        switch fileStatus {
        case .never:
            lock.lock()
            let insetSql = "insert into localEpubInfo(name,author,desc,cover,booktype,date,size,totaltime,isdel,issave,readRate) values(?,?,?,?,?,?,?,?,?,?,?)"
            do {
                model.isSave = false
                model.totalTime = 0
                model.date = String(format: "%f", arguments: [Date.currentTime])
                try fmdb.executeUpdate(insetSql, values: [model.fullname,
                                                          model.author ?? "",
                                                          model.desc ?? "",
                                                          model.cover ?? "",
                                                          model.booktype ?? "",
                                                          model.date ?? "",
                                                          model.filesize ?? "",
                                                          model.totalTime,
                                                          model.isdel,
                                                          model.isSave,
                                                          model.readRate ?? "0",
                    ])
            }catch {
                print(fmdb.lastErrorMessage())
            }
            lock.unlock()
        case .delete:
            updateLocalDB(model, type: .isDel)
        case .exist:
            print("数据已经存在，不能再次插入数据😅😅😅😅😅")
            return
        }
    }
    
    // 0：更新是否下载
    // 1：更新是否删除
    // 2：更新总时间
    func updateLocalDB(_ model:Model,type:UpdateType) {
        lock.lock()
        do{
            if type == .download {
                let updateSql = "UPDATE localEpubInfo SET isdel = ? where name = ?"
                try fmdb.executeUpdate(updateSql, values: [model.isdel,model.fullname])
            }else if type == .isDel {
                let updateSql = "UPDATE localEpubInfo SET isdel = ? where name = ?"
                try fmdb.executeUpdate(updateSql, values: [model.isdel,model.fullname])
            }else if type == .totalTime {
                let updateSql = "UPDATE localEpubInfo SET totaltime = ?,readRate = ? where name = ?"
                try fmdb.executeUpdate(updateSql, values: [model.totalTime,model.readRate!,model.fullname])
            }else if type == .issave {
                let updateSql = "UPDATE localEpubInfo SET issave = ? where name = ?"
                try fmdb.executeUpdate(updateSql, values: [model.isSave,model.fullname])
            }
        }catch {
            print(fmdb.lastErrorMessage())
        }
        lock.unlock()
    }
    
    // 0：过滤是否下载
    // 1：更新是否删除
    // 2：更新总时间
    func fetchLocalDB(queryType type:QueryType) ->[Model] {
        var tempArray = [Model]()
        var sql = "SELECT * FROM localEpubInfo"
        switch type {
        case .totalTime:
            sql = "SELECT * FROM localEpubInfo where totaltime > 0"
        case .queryAll:
            sql = "SELECT * FROM localEpubInfo"
        case .deleteTure:
            sql = "SELECT * FROM localEpubInfo where isdel = 1"
        case .deleteFalse:
            sql = "SELECT * FROM localEpubInfo where isdel = 0"
        case .saveTure:
            sql = "SELECT * FROM localEpubInfo where issave = 1"
        }
        
        do {
            // (name,author,desc,cover,date,size,totaltime,isdel,issave,readRate)
            let set = try fmdb.executeQuery(sql, values: nil)
            while set.next() {
                let urlName = set.string(forColumn: "name")!
                let size = set.string(forColumn: "size")
                let model = Model(urlName,size)
                model.author = set.string(forColumn: "author")
                model.desc = set.string(forColumn: "desc")
                model.cover = set.string(forColumn: "cover")
                model.date = set.string(forColumn: "date")
                model.totalTime = Int(set.int(forColumn: "totaltime"))
                model.booktype = set.string(forColumn: "booktype")
                model.isdel = set.bool(forColumn: "isdel")
                model.isSave = set.bool(forColumn: "issave")
                model.date = set.string(forColumn: "date")
                model.readRate = set.string(forColumn: "readRate")
                
                tempArray.append(model)
            }
        }catch {
            print(fmdb.lastErrorMessage())
        }
        
        return tempArray
    }
    
    /// 判断数据库中是否有当前数据
    func isHasDataInLocalDB(model:Model) -> FileStatus {
        let isHas = "SELECT * FROM localEpubInfo WHERE name = ?"
        do{
            let set = try fmdb.executeQuery(isHas, values: [model.fullname])
            //查找当前行，如果数据存在，则接着查找下一行
            if set.next() {
                if set.bool(forColumn: "isdel") {
                    return .delete
                }else {
                    return .exist
                }
            }else {
                return .never
            }
        }catch {
            print(fmdb.lastErrorMessage())
        }
        return .never
    }
    
    func deleteLocalDB(_ model:Model) {
        lock.lock()
        do{
            let deleteSql = "DELETE FROM localEpubInfo WHERE name = ?"
            try fmdb.executeUpdate(deleteSql, values: [model.fullname])
        }catch {
            print(fmdb.lastErrorMessage())
        }
        lock.unlock()
    }
    
    func deleteAllLocalDB() {
        lock.lock()
        do{
            try fmdb.executeUpdate("truncate table localEpubInfo", values: nil)
        }catch {
            print(fmdb.lastErrorMessage())
        }
        lock.unlock()
    }
}

//extension SQLHelper {
//    // MARK: - ********* 资源数据库操作 *********
//    func insertDataWith(model:Model) {
//        //加锁操作
//        lock.lock()
//        //sel语句
//        //(?,?)表示需要传的值，对应前面出现几个字段，后面就有几个问号
//        //(name,author,desc,cover,date,size,date,totaltime,isdel,issave,readRate)
//        let insetSql = "insert into epubInfo(name,author,urlname,cover,size) values(?,?,?,?,?)"
//        //更新数据库
//        do {
//            try fmdb.executeUpdate(insetSql, values: [model.title()!,model.author ?? "",model.fullname,model.cover ?? "",model.filesize ?? ""])
//        }catch {
//            print(fmdb.lastErrorMessage())
//        }
//
//        //解锁
//        lock.unlock()
//    }
//
//    //3.删
//    func deleteDataWith(model:Model) {
//        lock.lock()
//        do{
//            let deleteSql = "DELETE FROM epubInfo WHERE urlname = ?"
//            try fmdb.executeUpdate(deleteSql, values: [model.fullname])
//        }catch {
//            print(fmdb.lastErrorMessage())
//        }
//        lock.unlock()
//    }
//
//    // MARK: -- 不可更改，这个方法废掉了
//    func updateDataWith(model:Model) {
//        lock.lock()
//        //where id = ?中的id可传可不传
//        let updateSql = "UPDATE epubInfo SET save = ? where urlname = ?"
//        do{
//            try fmdb.executeUpdate(updateSql, values: [model.isSave, model.fullname!])
//        }catch {
//            print(fmdb.lastErrorMessage())
//        }
//        lock.unlock()
//    }
//
//    //5.判断数据库中是否有当前数据(查找一条数据)
//    func isHasDataInTable(model:Model) -> Bool {
//        let isHas = "SELECT * FROM epubInfo WHERE urlname = ?"
//        do{
//            let set = try fmdb.executeQuery(isHas, values: [model.fullname])
//            //查找当前行，如果数据存在，则接着查找下一行
//            if set.next() {
//                return true
//            }else {
//                return false
//            }
//        }catch {
//            print(fmdb.lastErrorMessage())
//        }
//        return true
//    }
//
//    //6.查找数据
//    func fetchData(_ count:Int = 10, _ isAll:Bool) ->[Model] {
//        var fetchSql:String
//        if isAll {
//            fetchSql = "SELECT * FROM epubInfo"
//        }else{
//            fetchSql = "SELECT * FROM epubInfo ORDER BY RANDOM() LIMIT \(count)"
//        }
//        //用于承接所有数据的临时数组
//        var tempArray = [Model]()
//        do {
//            let set = try fmdb.executeQuery(fetchSql, values: nil)
//            //循环遍历结果
//            while set.next() {
//                //给字段赋值
//                let urlName = set.string(forColumn: "urlname")!
//                let size = set.string(forColumn: "size")
//                let model = Model(urlName,size)
//                model.author = set.string(forColumn: "author")
//                model.cover = set.string(forColumn: "cover")
//                model.desc = set.string(forColumn: "description")
//                tempArray.append(model)
//            }
//        }catch {
//            print(fmdb.lastErrorMessage())
//        }
//
//        return tempArray
//    }
//
//    // Home控制器查询数据
//    func updateModelData(_ model:Model) {
//        do{
//            let isHas = "SELECT * FROM epubInfo where urlname = ?"
//            let set = try fmdb.executeQuery(isHas, values: [model.fullname])
//            //查找当前行，如果数据存在，则接着查找下一行
//            if set.next() {
//                //给字段赋值
//                model.author = set.string(forColumn: "author")
//                model.cover = set.string(forColumn: "cover")
//                model.desc = set.string(forColumn: "description")
//            }
//        }catch {
//            print(fmdb.lastErrorMessage())
//        }
//    }
//}
